IF OBJECTPROPERTY(OBJECT_ID('ssf.ddl_get_columns'), 'IsProcedure') = 1
	DROP PROCEDURE ssf.ddl_get_columns
GO

CREATE PROCEDURE ssf.ddl_get_columns
	@object_name NVARCHAR(512)
	, @delimiter NVARCHAR(32) = ','
	, @include_identity BIT = 1
	, @include_computed BIT = 1
	, @column_list NVARCHAR(MAX) OUTPUT
WITH RECOMPILE
AS

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@sql_error INT
			, @row_count BIGINT
			, @sql VARCHAR(MAX)
			, @nsql NVARCHAR(MAX)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @two_quotes CHAR(2) = CHAR(39) + CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE @db_name NVARCHAR(128) = ISNULL(PARSENAME(@object_name, 3), DB_NAME())
		, @schema_name NVARCHAR(128) = ISNULL(PARSENAME(@object_name, 2), SCHEMA_NAME())
		, @object_local_name NVARCHAR(128) = PARSENAME(@object_name, 1)
		, @exists_fl BIT
		, @is_table_fl BIT
-- ============

-- ============
/*<code_comment><![CDATA[verify parameters]]></code_comment>*/
IF NULLIF(@object_name, '') IS NULL
OR @delimiter IS NULL
OR @include_identity IS NULL
OR @include_computed IS NULL
BEGIN
	RAISERROR('Invalid or missing parameter.', 16, 1)
 	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[verify the object exists]]></code_comment>*/
EXECUTE @sql_error = ssf.ddl_exists_table_or_view
	@object_name = @object_name
	-- VARCHAR(512)
	, @exists_fl = @exists_fl OUTPUT
	-- BIT OUTPUT
	, @is_table_fl = @is_table_fl OUTPUT
	-- BIT OUTPUT

IF @exists_fl = 0
BEGIN
	RAISERROR('Object not found <%s>.', 16, 1, @object_name)
 	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[retrieve the list of columns]]></code_comment>*/
SET @column_list = ''

SET @nsql = 'SELECT @column_list = @column_list + c.[name] + ' + @quote + @delimiter + @quote
	+ @crlf + 'FROM ' + QUOTENAME(@db_name) + '.sys.schemas s(NOLOCK)'
	+ @crlf + 'JOIN ' + QUOTENAME(@db_name) + '.sys.' + CASE @is_table_fl WHEN 0 THEN 'views' ELSE 'tables' END + ' o(NOLOCK)'
	+ @crlf + 'ON o.[schema_id] = s.[schema_id]'
	+ @crlf + 'JOIN ' + QUOTENAME(@db_name) + '.sys.columns c(NOLOCK)'
	+ @crlf + 'ON c.[object_id] = o.[object_id]'
	+ @crlf + 'JOIN ' + QUOTENAME(@db_name) + '.INFORMATION_SCHEMA.COLUMNS isc(NOLOCK)'
	+ @crlf + 'ON isc.TABLE_NAME = o.[name]'
	+ @crlf + 'AND isc.COLUMN_NAME = c.[name]'
	+ @crlf + 'WHERE s.[name] = ' + QUOTENAME(@schema_name, @quote)
	+ @crlf + 'AND o.[name] = ' + QUOTENAME(@object_local_name, @quote)

IF @include_identity = 0
	SET @nsql +=
	@crlf + 'AND c.is_identity = 0'
IF @include_computed = 0
	SET @nsql +=
	@crlf + 'AND c.is_computed = 0'
	SET @nsql +=
	@crlf + 'ORDER BY isc.ORDINAL_POSITION'

PRINT @nsql

EXECUTE @sql_error = sp_executesql
	@nsql
	, N'@column_list NVARCHAR(MAX) OUTPUT'
	, @column_list OUTPUT

IF ISNULL(@sql_error, 1) <> 0
BEGIN
	RAISERROR('Dynamic sql failed <%s>.', 16, 1, @nsql)
 	RETURN 1
END
-- ============

-- ============
-- trim the last delimiter.
SET @column_list = LEFT(@column_list, (DATALENGTH(@column_list) - DATALENGTH(@delimiter)) / 2)
-- ============

-- end procedure body
-- ==============================================

RETURN @@ERROR
GO
